package com.scuvc.dao.impl;

import com.scuvc.dao.RecordInfoDao;
import com.scuvc.entity.*;
import com.scuvc.utils.ConnectionFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class RecordInfoDaoImpl implements RecordInfoDao {
    private QueryRunner queryRunner = new QueryRunner(ConnectionFactory.getDruidDataSource());

    // TODO 租车
    @Override
    public List<RecordInfo> queryRecordInfoByUserId(Long userId) {
        String sql = "SELECT re.id,car.carNo,b.brand_name,mo.model_name,cate.category_name," +
                "re.rent_date,re.return_date,re.price,re.rent_amount " +
                "FROM eh_carInfo car INNER JOIN eh_brandInfo b " +
                "ON car.brand_id=b.id INNER JOIN eh_modelInfo mo " +
                "ON car.model_id=mo.id INNER JOIN eh_categoryInfo cate " +
                "ON car.category_id=cate.id INNER JOIN eh_recordInfo re " +
                "ON car.id=re.car_id INNER JOIN eh_userInfo u " +
                "ON u.id=re.user_id WHERE u.id=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<RecordInfo>>() {
                @Override
                public List<RecordInfo> handle(ResultSet rs) throws SQLException {
                    List<RecordInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<RecordInfo> queryRecordInfoByUserIdByCarNo(Long userId, String carNo) {
        String sql = "SELECT re.id,car.carNo,b.brand_name,mo.model_name,cate.category_name," +
                "re.rent_date,re.return_date,re.price,re.rent_amount " +
                "FROM eh_carInfo car INNER JOIN eh_brandInfo b " +
                "ON car.brand_id=b.id INNER JOIN eh_modelInfo mo " +
                "ON car.model_id=mo.id INNER JOIN eh_categoryInfo cate " +
                "ON car.category_id=cate.id INNER JOIN eh_recordInfo re " +
                "ON car.id=re.car_id INNER JOIN eh_userInfo u " +
                "ON u.id=re.user_id WHERE u.id=? AND car.carNo=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<RecordInfo>>() {
                @Override
                public List<RecordInfo> handle(ResultSet rs) throws SQLException {
                    List<RecordInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, userId,carNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<RecordInfo> queryRecordInfoByUserIdByCarNo(Long userId, String carNo, int pageNumber, int pageSize) {
        String sql = "SELECT re.id,car.carNo,b.brand_name,mo.model_name,cate.category_name," +
                "re.rent_date,re.return_date,re.price,re.rent_amount " +
                "FROM eh_carInfo car INNER JOIN eh_brandInfo b " +
                "ON car.brand_id=b.id INNER JOIN eh_modelInfo mo " +
                "ON car.model_id=mo.id INNER JOIN eh_categoryInfo cate " +
                "ON car.category_id=cate.id INNER JOIN eh_recordInfo re " +
                "ON car.id=re.car_id INNER JOIN eh_userInfo u " +
                "ON u.id=re.user_id WHERE u.id=? AND car.carNo=? LIMIT ?,?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<RecordInfo>>() {
                @Override
                public List<RecordInfo> handle(ResultSet rs) throws SQLException {
                    List<RecordInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, userId,carNo,pageNumber,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<RecordInfo> queryRecordInfoByUserIdPage(Long userId,int pageNumber, int pageSize) {
        int offset = (pageNumber -1 )*pageSize;
        String sql = "SELECT re.id,car.carNo,b.brand_name,mo.model_name,cate.category_name," +
                "re.rent_date,re.return_date,re.price,re.rent_amount " +
                "FROM eh_carInfo car INNER JOIN eh_brandInfo b " +
                "ON car.brand_id=b.id INNER JOIN eh_modelInfo mo " +
                "ON car.model_id=mo.id INNER JOIN eh_categoryInfo cate " +
                "ON car.category_id=cate.id INNER JOIN eh_recordInfo re " +
                "ON car.id=re.car_id INNER JOIN eh_userInfo u " +
                "ON u.id=re.user_id WHERE u.id=? LIMIT ?,?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<RecordInfo>>() {
                @Override
                public List<RecordInfo> handle(ResultSet rs) throws SQLException {
                    List<RecordInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, userId,offset,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void setResultSet(ResultSet rs, List<RecordInfo> list) throws SQLException {
        while(rs.next()){
            RecordInfo recordInfo = new RecordInfo();
            recordInfo.setId(rs.getLong("id"));

            CarInfo carInfo = new CarInfo();
            carInfo.setCarNo(rs.getString("carNo"));

            BrandInfo brandInfo = new BrandInfo();
            brandInfo.setBrand_name(rs.getString("brand_name"));
            carInfo.setBrand(brandInfo);

            ModelInfo modelInfo = new ModelInfo();
            modelInfo.setModel_name(rs.getString("model_name"));
            carInfo.setModel(modelInfo);

            CategoryInfo categoryInfo = new CategoryInfo();
            categoryInfo.setCategory_name(rs.getString("category_name"));
            carInfo.setCategory(categoryInfo);
            recordInfo.setCarInfo(carInfo);

            recordInfo.setRent_date(rs.getDate("rent_date"));
            recordInfo.setReturn_date(rs.getDate("return_date"));
            recordInfo.setPrice(rs.getLong("price"));
            recordInfo.setRent_amount(rs.getLong("rent_amount"));
            list.add(recordInfo);
        }
    }
    @Override
    public List<RecordInfo> queryRecordInfoByUserIdNoReturn(Long userId) {
        String sql = "SELECT re.id,car.carNo,b.brand_name,mo.model_name,cate.category_name,car.price," +
                "car.deposit,re.rent_date,re.return_date,re.price,re.rent_amount " +
                "FROM eh_carInfo car INNER JOIN eh_brandInfo b " +
                "ON car.brand_id=b.id INNER JOIN eh_modelInfo mo " +
                "ON car.model_id=mo.id INNER JOIN eh_categoryInfo cate " +
                "ON car.category_id=cate.id INNER JOIN eh_recordInfo re " +
                "ON car.id=re.car_id INNER JOIN eh_userInfo u " +
                "ON u.id=re.user_id WHERE u.id=? AND re.return_date IS NULL";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<RecordInfo>>() {
                @Override
                public List<RecordInfo> handle(ResultSet rs) throws SQLException {
                    List<RecordInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<RecordInfo> queryRecordInfoById(Long id) {
        String sql = "SELECT * FROM eh_recordInfo WHERE id=?";
        try {
            return queryRunner.query(sql,new BeanListHandler<RecordInfo>(RecordInfo.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null ;
    }

    // TODO 还车

    public static void main(String[] args) {
        RecordInfoDao r = new RecordInfoDaoImpl();
        List<RecordInfo> res = r.queryRecordInfoByUserIdNoReturn(6L);
        //List<RecordInfo> list = r.queryRecordInfoByUserIdNoReturn(6L);
        for(RecordInfo rescord:res){
            System.out.println(rescord);
        }
    }
}
